<?php
/**
 * CustomerController is used for management of customers.
 * 
 * LBB Solutions Oy 版权所有 © 2013
 */
class CustomerController extends Controller
{
	/**
	 * @return array action filters
	 */
	public function filters() {
		return array(
			'accessControl', // perform access control for CRUD operations
			array(
                'COutputCache + nearby', // Cache for actionNearby
                'duration' => 600, // 10 mins
				'varyByParam' => array('id'),
				'varyByExpression' => 'Yii::app()->language',
				'varyBySession' => true,
            ),
		);
	}
	
	/**
	 * Specifies the access control rules.
	 * This method is used by the 'accessControl' filter.
	 * @return array access control rules
	 */
	public function accessRules() {
		return array(
			array('allow', // allow authenticated user to perform below actions
				'users'=>array('@'),
			),
			array('deny',  // deny all users
				'users'=>array('*'),
			),
		);
	}
	
	/**
	 * Show layout.
	 */
	public function actionIndex() {
		//handle page=?
		$page_size = Yii::app()->params['pageSize'];
		$current_page = Yii::app()->request->getQuery('page', 1);
		$offset = ($current_page - 1) * $page_size;
		$sql_limit = " LIMIT {$offset}, {$page_size} ";
		
		$limit = (int)Yii::app()->request->getQuery('limit');
		if ($limit > 0) {
			$sql_limit = " LIMIT {$limit} ";
		}
		
		//parameter
		$params = array(
			':id_merchant'=>Yii::app()->user->id,
		);
		
		//handle the search q=?
		$s_where = '';
		$keywords = trim(Yii::app()->request->getQuery('q'));
		if (!empty($keywords)) {
			$s_where = ' AND (c.id_user=:uid OR u.mobile LIKE :keyword OR u.email LIKE :keyword OR u.name LIKE :keyword) ';
			$params[':uid'] = $keywords;
			$params[':keyword'] = "%{$keywords}%";
		}
		
		//handle the name=? (today, week, month, all)
		$type_name = Yii::app()->request->getQuery('name');
		if ($type_name == 'today') {
			$s_where .= ' AND DATE(c.update_time)=CURDATE() ';
		} elseif ($type_name == 'yesterday') {
			$s_where .= ' AND DATE(c.update_time)=DATE_SUB(CURDATE(), INTERVAL 1 DAY) ';
		} elseif ($type_name == 'last_7') {
			$s_where .= ' AND DATE(c.update_time)>DATE_SUB(CURDATE(), INTERVAL 7 DAY) ';
		} elseif ($type_name == 'last_28') {
			$s_where .= ' AND DATE(c.update_time)>DATE_SUB(CURDATE(), INTERVAL 28 DAY) ';
		}
		
		//customer notes list
		$sql_customers = 'SELECT c.id_user, c.amount, c.point, c.visit_times, c.notes, UNIX_TIMESTAMP(c.update_time) AS ts_last_time,
				u.name, u.mobile, u.email, u.gender, u.picture, f.id AS followed
			FROM tbl_visit_customer_notes c
			INNER JOIN tbl_user_account u ON c.id_user=u.id
			LEFT JOIN tbl_user_follow_merchant f ON f.id_user=c.id_user AND f.id_merchant=c.id_merchant
			WHERE c.id_merchant=:id_merchant '
			. $s_where
			. ' ORDER BY c.update_time DESC'
			. $sql_limit;
		$sql_count = 'SELECT COUNT(c.id_user) AS total FROM tbl_visit_customer_notes c
			INNER JOIN tbl_user_account u ON c.id_user=u.id
			WHERE c.id_merchant=:id_merchant ' . $s_where;
		
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_count);
		$aTotal = $command->queryRow(true, $params);
		$command = $connection->createCommand($sql_customers);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		//set pager
		$pages = new CPagination($aTotal['total']);
		$pages->setPageSize($page_size);
		if (Yii::app()->request->isAjaxRequest) {
			$this->renderPartial('_index', array('rows'=>$rows, 'pages'=>$pages, 'total'=>$aTotal['total']));
		} else {
			$this->render('index', array('rows'=>$rows, 'pages'=>$pages, 'total'=>$aTotal['total']));
		}
	}
	
	/**
	 * Show layout.
	 */
	public function actionList() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		$limit = (int)Yii::app()->request->getParam('limit');
		if ($limit > 0) {
			$sql_limit = " LIMIT {$limit} ";
		} else {
			$sql_limit = " LIMIT 5 ";
		}
		
		//parameter
		$params = array(
			':id_merchant'=>Yii::app()->user->id,
		);
		
		//customer notes list
		$sql_customers = 'SELECT c.id_user, c.amount, c.point, c.visit_times, c.notes, UNIX_TIMESTAMP(c.update_time) AS ts_last_time,
				u.name, u.mobile, u.email, u.gender, u.picture, f.id AS followed
			FROM tbl_visit_customer_notes c
			INNER JOIN tbl_user_account u ON c.id_user=u.id
			LEFT JOIN tbl_user_follow_merchant f ON f.id_user=c.id_user AND f.id_merchant=c.id_merchant
			WHERE c.id_merchant=:id_merchant '
			. ' ORDER BY c.update_time DESC'
			. $sql_limit;
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_customers);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		//set pager
		$this->renderPartial('_list', array('rows'=>$rows));
	}
	
	/**
	 * Suggest a customer list
	 */
	public function actionSuggest() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		$limit = (int)Yii::app()->request->getParam('limit');
		if ($limit > 0) {
			$sql_limit = " LIMIT {$limit} ";
		} else {
			$sql_limit = " LIMIT 5 ";
		}
		
		//parameter
		$params = array(
			':id_merchant'=>Yii::app()->user->id,
		);
		
		//handle the search q=?
		$s_where = '';
		$keywords = trim(Yii::app()->request->getQuery('q'));
		if (!empty($keywords)) {
			//check the 1st char is 0 or nor
			$firstChar = substr($keywords, 0, 1);
			if ($firstChar == '0') {
				$keywords = substr($keywords, 1);//remove the first '0'.
			}
			$s_where = ' AND (c.id_user=:uid OR u.mobile LIKE :keyword OR u.email LIKE :keyword OR u.name LIKE :keyword) ';
			$params[':uid'] = $keywords;
			$params[':keyword'] = "%{$keywords}%";
		}
		
		//customer notes list
		$sql_customers = 'SELECT c.id_user, c.amount, c.point, c.visit_times, c.notes, UNIX_TIMESTAMP(c.update_time) AS ts_last_time,
				u.name, u.mobile, u.email, u.gender, u.picture, f.id AS followed
			FROM tbl_visit_customer_notes c
			INNER JOIN tbl_user_account u ON c.id_user=u.id
			LEFT JOIN tbl_user_follow_merchant f ON f.id_user=c.id_user AND f.id_merchant=c.id_merchant
			WHERE c.id_merchant=:id_merchant '
			. $s_where
			. ' ORDER BY c.update_time DESC'
			. $sql_limit;
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_customers);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$type = Yii::app()->request->getQuery('type');
		if ($type == 'json') {
			$this->renderPartial('_suggestJson', array('rows'=>$rows));
		} else {
			$this->renderPartial('_suggest', array('rows'=>$rows));
		}
	}
	
	/**
	 * Report
	 */
	public function actionReport() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		$type_name = Yii::app()->request->getParam('type');
		if ($type_name == 'today') {
			$sql_visits_rpt = 'SELECT local_date, local_hour, visits, amount
				FROM tbl_visit_report_hourly
				WHERE id_merchant=:id_merchant AND local_date=CURDATE()';
		} elseif ($type_name == 'yesterday') {
			$sql_visits_rpt = 'SELECT local_date, local_hour, visits, amount
				FROM tbl_visit_report_hourly
				WHERE id_merchant=:id_merchant AND local_date=DATE_SUB(CURDATE(), INTERVAL 1 DAY)';
		} elseif ($type_name == 'last_7') {
			$sql_visits_rpt = 'SELECT local_date, visits, amount
				FROM tbl_visit_report_daily
				WHERE id_merchant=:id_merchant AND local_date>DATE_SUB(CURDATE(), INTERVAL 7 DAY)';
		} elseif ($type_name == 'last_28') {
			$sql_visits_rpt = 'SELECT local_date, visits, amount
				FROM tbl_visit_report_daily
				WHERE id_merchant=:id_merchant AND local_date>DATE_SUB(CURDATE(), INTERVAL 28 DAY)';
		} else {
			throw new CHttpException(404, 'The requested page does not exist!');
		}
		//parameter
		$params = array(
			':id_merchant'=>Yii::app()->user->id,
		);
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_visits_rpt);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		//set pager
		if ($type_name == 'today' or $type_name == 'yesterday') {
			$this->renderPartial('_chart_hourly', array('rows'=>$rows));
		} else {
			$this->renderPartial('_chart_daily', array('rows'=>$rows, 'type'=>$type_name));
		}
	}
	
	/**
	 * AJAX: show the notes from a given customer id: uid=?.
	 */
	public function actionNotes() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		//id_user=?
		$id_user = Yii::app()->request->getParam('uid');
		if ($id_user < 1) {
			throw new CHttpException(404, 'This customer "'.$id_user.'" does not exist.');
		}
		
		//POST: update notes
		if (Yii::app()->request->getPost('update') == 1) {
			$notes = trim(Yii::app()->request->getPost('notes'));
			//update notes
			$sql_update = 'UPDATE tbl_visit_customer_notes
				SET notes=:notes
				WHERE id_merchant=:id_merchant AND id_user=:id_user';
			$connection = Yii::app()->db;
			$command = $connection->createCommand($sql_update);
			$command->execute(array(
				':id_merchant'=>Yii::app()->user->id,
				':id_user' => $id_user,
				':notes' => $notes,
			));
			$connection->active = false;
			
			echo strip_tags($notes);
		} else {
			//fetch notes
			$params = array(
				':id_merchant'=>Yii::app()->user->id,
				':id_user' => $id_user,
			);
			$sql_notes = 'SELECT id_user, notes, id_location FROM tbl_visit_customer_notes WHERE id_merchant=:id_merchant AND id_user=:id_user';
			$connection = Yii::app()->db;
			$command = $connection->createCommand($sql_notes);
			$row = $command->queryRow(true, $params);
			$connection->active = false;

			$this->renderPartial('_notes', array('r'=>$row));
		}
	}
	
	/**
	 * Get recent customers. Only ajax.
	 */
	public function actionRecent() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		//how many customers recently: limit 6
		$sql_customers = 'SELECT DISTINCT d.id_user, d.lang, u.email, u.name, u.site_user_id, u.gender, u.picture,
			  MAX(UNIX_TIMESTAMP(r.redeemed_time)) AS ts_last_time, COUNT(r.id) AS visit_times, f.id AS followed,
			  SUM(s.price) AS amount
			FROM tbl_merchant_special_redeem r
			INNER JOIN tbl_merchant_special s ON r.id_special=s.id
			INNER JOIN tbl_user_device_settings d ON (d.device_token=r.device_token OR d.id_user=r.id_user)
			LEFT JOIN tbl_user_account u ON u.id=d.id_user
			LEFT JOIN tbl_user_follow_merchant f ON f.id_user=d.id_user AND f.id_merchant=s.id_merchant
			WHERE s.id_merchant=:id_merchant AND s.status=1 AND r.redeemed=2
			GROUP BY d.id_user
			ORDER BY ts_last_time DESC
			LIMIT 5';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_customers);
		$rows = $command->queryAll(true, array(
			':id_merchant'=>Yii::app()->user->id,
		));
		$connection->active = false;
		
		$this->renderPartial('_recent', array(
			'rows' => $rows,
		));
	}
	
	/**
	 * Get recent customers. Only ajax.
	 */
	public function actionRecentAmount() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		//how many customers recently: limit 6
		$sql_customers = 'SELECT DISTINCT t.id_user, u.email, u.name, u.gender, u.picture, f.id AS followed,
			  SUM(t.amount) AS amounts, SUM(t.point) AS points, MAX(UNIX_TIMESTAMP(t.create_time)) AS ts_last_time, 
			  count(t1.id) AS visit_times
			FROM tbl_visit_transaction t
			LEFT JOIN tbl_user_account u ON u.id=t.id_user
			LEFT JOIN tbl_visit_transaction t1 ON u.id=t.id_user AND u1.type=1
			LEFT JOIN tbl_user_follow_merchant f ON f.id_user=t.id_user AND f.id_merchant=s.id_merchant
			WHERE s.id_merchant=:id_merchant AND s.status=1 AND r.redeemed=2
			GROUP BY d.id_user
			ORDER BY ts_last_time DESC
			LIMIT 5';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_customers);
		$rows = $command->queryAll(true, array(
			':id_merchant'=>Yii::app()->user->id,
		));
		$connection->active = false;
		
		$this->renderPartial('_recent', array(
			'rows' => $rows,
		));
	}
	
	/**
	 * Get people nearby. Only ajax. 10 mins refresh.
	 */
	public function actionNearby() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		//check the merchant's location
		$latitude = Yii::app()->request->getQuery('lat');
		$longitude = Yii::app()->request->getQuery('lng');
		if (!empty($latitude) and !empty($longitude)) {
			//how many customers recently: limit 5
			$sql_customers = 'SELECT COUNT(d.device_token) AS number
				FROM tbl_user_device_settings d
				WHERE d.latitude IS NOT NULL AND d.longitude IS NOT NULL
				  AND UNIX_TIMESTAMP(d.update_time)>:ts_10m_ago
				  AND (6371 * acos(cos(radians(:lat)) * cos(radians(d.latitude)) * cos(radians(d.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(d.latitude)))) < 1.5';
			$params = array(
				':ts_10m_ago' => time() - 600,
				':lat' => $latitude,
				':lng' => $longitude,
			);
			$connection = Yii::app()->db;
			$command = $connection->createCommand($sql_customers);
			$row = $command->queryRow(true, $params);
			$connection->active = false;
			
			$this->renderPartial('_nearby', array(
				'row' => $row,
			));
			
			//DEBUG info:
			$total = $row['number'];
			if ($total > 9) {
				//send email
				$subject = "Found {$total} people nearby : " . Yii::app()->user->name . ' : ' . date('Y-m-d H:i:s');
				$message = array(
					'id_merchant' => Yii::app()->user->id,
					'merchant_name' => Yii::app()->user->name,
					'rows' => $row,
				);
				$body = '<pre>' . print_r($message, 1) . '</pre>';
				BUtils::EmailAdmin($subject, $body);
			}
		} else {
			$this->renderPartial('_nearby');
		}
	}
}